package co.fitstart.admin.dao;

import java.util.Date;
import java.util.List;
import java.util.Map;

import com.demo2do.core.persistence.annotation.Dynamic;
import com.demo2do.core.persistence.annotation.Limit;
import com.demo2do.core.persistence.annotation.Query;
import com.demo2do.core.persistence.annotation.Variable;

import co.fitstart.entity.course.CourseType;
import co.fitstart.entity.course.schedule.CourseSchedule;
import co.fitstart.entity.course.schedule.ScheduleStatus;
import co.fitstart.entity.user.TelephoneResource;
import co.fitstart.entity.user.User;

/**
 * 
 * @author haiyan_xiao
 *
 */
@Dynamic("hibernate")
public interface TelephoneResourceDao {
    
    /**
     * 
     * @param begin
     * @param end
     * @param beginIndex
     * @param maxResult
     * @return
     */
    @Limit
    @Query("FROM TelephoneResource WHERE disabled = false AND collectDate>=:begin AND collectDate<=:end ORDER BY collectDate DESC")
    public List<TelephoneResource> list(@Variable("begin") Date begin, @Variable("end") Date end, @Variable("beginIndex") int beginIndex, @Variable("maxResult") int maxResult);
    
    /**
     * 
     * @param begin
     * @param end
     * @param experienced
     * @param beginIndex
     * @param maxResult
     * @return
     */
    @Limit
    @Query("FROM TelephoneResource WHERE disabled = false AND collectDate>=:begin AND collectDate<=:end AND experienced=:experienced ORDER BY collectDate DESC")
    public List<TelephoneResource> list(@Variable("begin") Date begin, @Variable("end") Date end, @Variable("experienced") boolean experienced, @Variable("beginIndex") int beginIndex, @Variable("maxResult") int maxResult);
    
    /**
     * 
     * @param begin
     * @param end
     * @return
     */
    @Query("SELECT COUNT(*) FROM TelephoneResource WHERE disabled = false AND collectDate>=:begin AND collectDate<=:end")
    public int count(@Variable("begin") Date begin, @Variable("end") Date end);
    
    /**
     * 
     * @param begin
     * @param end
     * @param experienced
     * @return
     */
    @Query("SELECT COUNT(*) FROM TelephoneResource WHERE disabled = false AND collectDate>=:begin AND collectDate<=:end AND experienced=:experienced")
    public int count(@Variable("begin") Date begin, @Variable("end") Date end, @Variable("experienced") boolean experienced);
    
    /**
     * 
     * @param begin
     * @param end
     * @return
     */
    @Query("FROM TelephoneResource WHERE disabled = false AND collectDate>=:begin AND collectDate<=:end ORDER BY collectDate DESC")
    public List<TelephoneResource> list(@Variable("begin") Date begin, @Variable("end") Date end);
    
    /**
     * 
     * @param begin
     * @param end
     * @param experienced
     * @return
     */
    @Query("FROM TelephoneResource WHERE disabled = false AND collectDate>=:begin AND collectDate<=:end AND experienced=:experienced ORDER BY collectDate DESC")
    public List<TelephoneResource> list(@Variable("begin") Date begin, @Variable("end") Date end, @Variable("experienced") boolean experienced);
    
    /**
     * 
     * @param users
     * @param beginIndex
     * @param maxResult
     * @return
     */
    @Limit
    @Query("FROM TelephoneResource WHERE disabled = false AND user IN (:users) ORDER BY collectDate DESC")
    public List<TelephoneResource> listIn(@Variable("users") List<User> users, @Variable("beginIndex") int beginIndex, @Variable("maxResult") int maxResult);
    
    /**
     * 
     * @param users
     * @return
     */
    @Query("FROM TelephoneResource WHERE disabled = false AND user IN (:users) ORDER BY collectDate DESC")
    public List<TelephoneResource> listIn(@Variable("users") List<User> users);
    
    /**
     * 
     * @param users
     * @param beginIndex
     * @param maxResult
     * @return
     */
    @Limit
    @Query("FROM TelephoneResource WHERE disabled = false AND collectDate>=:begin AND collectDate<=:end AND user NOT IN (:users) ORDER BY collectDate DESC")
    public List<TelephoneResource> listNotIn(@Variable("begin") Date begin, @Variable("end") Date end, @Variable("users") List<User> users, @Variable("beginIndex") int beginIndex, @Variable("maxResult") int maxResult);
    
    /**
     * 
     * @param users
     * @return
     */
    @Query("FROM TelephoneResource WHERE disabled = false AND collectDate>=:begin AND collectDate<=:end AND user NOT IN (:users) ORDER BY collectDate DESC")
    public List<TelephoneResource> listNotIn(@Variable("begin") Date begin, @Variable("end") Date end, @Variable("users") List<User> users);
    
    /**
     * 
     * @param begin
     * @param end
     * @return
     */
    @Query("SELECT resource.user FROM TelephoneResource resource WHERE resource.disabled = false AND resource.collectDate>=:begin AND resource.collectDate<=:end ORDER BY resource.collectDate DESC")
    public List<User> listUser(@Variable("begin") Date begin, @Variable("end") Date end);
    
    /**
     * 
     * @param begin
     * @param end
     * @param experienced
     * @return
     */
    @Query("SELECT resource.user FROM TelephoneResource resource WHERE resource.disabled = false AND resource.collectDate>=:begin AND resource.collectDate<=:end AND resource.experienced=:experienced ORDER BY resource.collectDate DESC")
    public List<User> listUser(@Variable("begin") Date begin, @Variable("end") Date end, @Variable("experienced") boolean experienced);
    
    /**
     * 
     * @param courseType
     * @param scheduleStatus
     * @param users
     * @return
     */
    @Query("SELECT schedule.student.user FROM CourseSchedule schedule WHERE schedule.disabled = false AND schedule.courseType = :courseType AND schedule.scheduleStatus = :scheduleStatus AND schedule.course.experience = true AND schedule.student.user IN (:users)")
    public List<User> listExperiencedUser(@Variable("courseType") CourseType courseType, @Variable("scheduleStatus") ScheduleStatus scheduleStatus, @Variable("users") List<User> users);
    
    /**
     * 
     * @param courseType
     * @param scheduleStatus
     * @param users
     * @return
     */
    @Query("FROM CourseSchedule WHERE disabled = false AND courseType = :courseType AND course.experience=true AND scheduleStatus = :scheduleStatus AND student.user IN (:users) ORDER BY student,occurDate ASC")
    public List<CourseSchedule> listCourseSchedule(@Variable("courseType") CourseType courseType, @Variable("scheduleStatus") ScheduleStatus scheduleStatus, @Variable("users") List<User> users);
    
    /**
     * 
     * @param courseType
     * @param scheduleStatus
     * @param users
     * @return
     */
    @Query("FROM CourseSchedule WHERE disabled = false AND courseType = :courseType AND course.experience=true AND scheduleStatus IN (:scheduleStatus) AND student.user IN (:users) ORDER BY student,occurDate DESC")
    public List<CourseSchedule> listCourseSchedule(@Variable("courseType") CourseType courseType, @Variable("scheduleStatus") List<ScheduleStatus> scheduleStatus, @Variable("users") List<User> users);
    
    /**
     * 
     * @param users
     * @return
     */
    @Query("SELECT new Map(student.user.id AS id, COUNT(id) AS total) FROM CourseRound WHERE disabled = false AND course.experience = false GROUP BY student")
    public List<Map<String, Object>> listRoundCount(@Variable("users") List<User> users);
    
    /**
     * 
     * @param users
     * @return
     */
    @Query("SELECT new Map(round.student.user.id AS id, SUM(round.order.price) AS price) FROM CourseRound round WHERE round.disabled = false AND round.course.experience = false AND round.order IS NOT NULL GROUP BY round.student")
    public List<Map<String, Object>> listRoundPriceSum(@Variable("users") List<User> users);
    
    /**
     * 
     * @param telephoneResources
     * @return
     */
    @Query("SELECT new Map(telephoneResource.id AS id, COUNT(id) AS total) FROM TelephoneRecord WHERE disabled = false AND telephoneResource IN (:telephoneResources) GROUP BY telephoneResource")
    public List<Map<String, Object>> listTelephoneRecordCount(@Variable("telephoneResources") List<TelephoneResource> telephoneResources);
    
    /**
     * 
     * @param begin
     * @param end
     * @return
     */
    @Query("SELECT new Map(telephoneResource.id AS id, COUNT(id) AS total) FROM TelephoneRecord WHERE disabled = false AND telephoneResource.disabled = false AND telephoneResource.collectDate>=:begin AND telephoneResource.collectDate<=:end GROUP BY telephoneResource")
    public List<Map<String, Object>> listTelephoneRecordCount(@Variable("begin") Date begin, @Variable("end") Date end);
    
    /**
     * 
     * @param begin
     * @param end
     * @param experienced
     * @return
     */
    @Query("SELECT new Map(telephoneResource.id AS id, COUNT(id) AS total) FROM TelephoneRecord WHERE disabled = false AND telephoneResource.disabled = false AND telephoneResource.collectDate>=:begin AND telephoneResource.collectDate<=:end AND telephoneResource.experienced = :experienced GROUP BY telephoneResource")
    public List<Map<String, Object>> listTelephoneRecordCount(@Variable("begin") Date begin, @Variable("end") Date end, @Variable("experienced") boolean experienced);
}
